<?php
/**
 * this is a class for operating hierarchical data
 * mehtods in this class should extends in other class
 * nested.sql in doc directory is useful
 */
class HierarchicalUtil {
	/**
	 * 插入子节点（php中是否可以执行多行sql语句？）
	 */
	public static function insert() {
		/**
		LOCK TABLE nested_category WRITE;
		//如果不能用php直接运行这样的sql，可以用单独的函数来获取left和right值
		SELECT @myRight := rgt FROM nested_category
		WHERE name = 'TELEVISIONS';

		UPDATE nested_category SET rgt = rgt + 2 WHERE rgt >= @myRight;
		UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

		INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight, @myRight + 1);

		UNLOCK TABLES;
		*/
	}
	
	/**
	 * 删除节点（包括子节点）
	 */
	public static function deleteAll() {
		/**
		LOCK TABLE nested_category WRITE;

		SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
		FROM nested_category
		WHERE name = 'GAME CONSOLES';

		DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

		UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
		UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

		UNLOCK TABLES;
		*/
	}
	
	/**
	 * 删除节点（不包括子节点，子节点提到被删节点同级）
	 */
	public static function deleteOnly() {
		/**
		LOCK TABLE nested_category WRITE;

		SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
		FROM nested_category
		WHERE name = 'PORTABLE ELECTRONICS';

		DELETE FROM nested_category WHERE lft = @myLeft;

		UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
		UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
		UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

		UNLOCK TABLES;
		*/
	}
	
	/**
	 * 获取所有子孙节点
	 */
	public static function getAllSub() {
		/**
		SELECT node.name
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		AND parent.name = 'ELECTRONICS'
		ORDER BY node.lft;
		*/
	}
	
	/**
	 * 获取直接子节点
	 */
	public static function getDirectSub() {
		/**
		SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
		FROM nested_category AS node,
			nested_category AS parent,
			nested_category AS sub_parent,
			(
				SELECT node.name, (COUNT(parent.name) - 1) AS depth
				FROM nested_category AS node,
				nested_category AS parent
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
				AND node.name = 'PORTABLE ELECTRONICS'
				GROUP BY node.name
				ORDER BY node.lft
			)AS sub_tree
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
			AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
			AND sub_parent.name = sub_tree.name
		GROUP BY node.name
		HAVING depth = 1
		ORDER BY node.lft;
		*/
	}
	
	/**
	 * 获取所有叶节点
	 */
	public static function getAllLeaf() {
		/**
		SELECT name
		FROM nested_category
		WHERE rgt = lft + 1;
		*/
	}
	
	/**
	 * 获取根结点到某个节点之间的所有节点列表
	 * 生成面包屑特别方便
	 */
	public static function getPath() {
		/**
		SELECT parent.name
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		AND node.name = 'FLASH'
		ORDER BY parent.lft;
		*/
	}
	
	/**
	 * 获取节点的深度
	 */
	public static function getDepth() {
		/**
		SELECT node.name, (COUNT(parent.name) - 1) AS depth
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		GROUP BY node.name
		ORDER BY node.lft;
		*/
	}
	
	/**
	 * 获取节点及其子节点的深度（直接子节点的深度为1）
	 */
	public static function getSubDepth() {
		/**
		SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
		FROM nested_category AS node,
			nested_category AS parent,
			nested_category AS sub_parent,
			(
				SELECT node.name, (COUNT(parent.name) - 1) AS depth
				FROM nested_category AS node,
				nested_category AS parent
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
				AND node.name = 'PORTABLE ELECTRONICS'
				GROUP BY node.name
				ORDER BY node.lft
			)AS sub_tree
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
			AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
			AND sub_parent.name = sub_tree.name
		GROUP BY node.name
		ORDER BY node.lft;
		*/
	}
	
	/**
	 * 生成格式化的列表（根据深度适当缩进）
	 */
	public static function getFormatTree() {
		/**
		SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		GROUP BY node.name
		ORDER BY node.lft;
		*/
	}
	
	/**
	 * 获取类别及类别下商品数量（获取商品列表也差不多）
	 */
	public static function getProductCount() {
		/**
		SELECT parent.name, COUNT(product.name)
		FROM nested_category AS node ,
		nested_category AS parent,
		product
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		AND node.category_id = product.category_id
		GROUP BY parent.name
		ORDER BY node.lft;
		*/
	}
}
?>